package com.cola.dao;

import com.cola.entity.JoinSaleActivity;
import com.cola.entity.courier.Courier;
import com.cola.entity.order.Order;
import com.cola.entity.order.OrderDetail;
import com.cola.enums.OrderType;
import com.cola.utils.DBControl;
import com.cola.utils.baiduMap.entity.Result;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.List;

/**
 *
 * @author Max
 * @since 2017/9/18
 */
public class CourierDao extends BaseDao {
    private final Logger logger =  LoggerFactory.getLogger(getClass());

    private static CourierDao CourierDao = new CourierDao();

    private CourierDao(){}

    public static CourierDao getInstance(){
        return CourierDao;
    }

    /**
     * 保存配送员的基本信息
     * @return Long 该配送员的courier_id
     * @author sunny
     * @since 2017/9/14 14:25
     */
    public Long saveCourierInfo(String courier_name,String mep_no,String courier_mobile) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Long courier_id = 0l;
        Connection conn=null;
        try {
            conn = DBControl.getConnection();
            String sql = "insert into cola_courier(courier_name,mep_no,courier_mobile,status,create_time)values(?,?,?,1,now())";
            ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1,courier_name);
            ps.setString(2,mep_no);
            ps.setString(3,courier_mobile);
            ps.execute();
            rs = ps.getGeneratedKeys();
            while (rs.next()){
                courier_id = rs.getLong(1);
            }
        } catch (Exception e) {
            logger.error("新增配送员信息异常！",e);
        } finally {
            DbUtils.closeQuietly(conn,ps,rs);
        }

        return courier_id;
    }

    /**
     * 更新配送员信息
     * @return boolean:true为成功；false为失败
     * @author sunny
     * @since 2017/9/14 15:00
     */
    public Boolean updateCourierInfo(Integer courier_id,String courier_wechat_nick,String courier_open_id,String courier_head_img_url) {
        PreparedStatement ps = null;
        Connection conn=null;
        boolean result=false;
        try {
            conn = DBControl.getConnection();
            String sql = "update cola_courier set courier_wechat_nick=?,courier_open_id=?,courier_head_img_url=?,update_time=now() where courier_id=? ";
            ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1,courier_wechat_nick);
            ps.setString(2,courier_open_id);
            ps.setString(3,courier_head_img_url);
            ps.setLong(4,courier_id);
            if(ps.executeUpdate()>0){
                result=true;
            }
        } catch (Exception e) {
            logger.error("更新配送员信息异常！",e);
        } finally {
            DbUtils.closeQuietly(conn,ps,null);
        }
        return result;
    }

    /**
     * 删除配送员信息
     * @return boolean:true为成功；false为失败
     * @author sunny
     * @since 2017/9/14 15:00
     */
    public Boolean deteleCourier(Integer courier_id) {
        Connection conn=null;
        try {
            conn = DBControl.getConnection();
            String sql = "update cola_courier set status=-2 where courier_id=? ";
            return getQueryRunner().update(conn,sql,courier_id)>0;
        } catch (Exception e) {
            logger.error("更新配送员信息异常！",e);
        } finally {
            DbUtils.closeQuietly(conn,null,null);
        }
        return false;
    }

    /**
     * 更新配送员信息
     * @return boolean:true为成功；false为失败
     * @author sunny
     * @since 2017/9/14 15:00
     */
    public Boolean updateCourierInfo(Integer courier_id,String mobile,String courier_name) {
        PreparedStatement ps = null;
        Connection conn=null;
        try {
            conn = DBControl.getConnection();
            String sql = "update cola_courier set courier_mobile=?,courier_name=? where courier_id=? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1,mobile);
            ps.setString(2,courier_name);
            ps.setInt(3,courier_id);
            return ps.executeUpdate()>0;
        } catch (Exception e) {
            logger.error("更新配送员信息异常！",e);
        } finally {
            DbUtils.closeQuietly(conn,ps,null);
        }
        return false;
    }

    /**
     * 获取配送员列表
     * @param mepNo 经销商编号
     * @return List<Courier>
     * @author Max
     * @since 2017/9/18
     */
    public List<Courier> getCourierList(String mepNo){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "select * from cola_courier where mep_no = ? and status=1 and type =1";
            return getQueryRunner().query(conn,sql,new BeanListHandler<Courier>(Courier.class),mepNo);
        }catch (Exception e){
            logger.error("获取配送员列表异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }

    /**
     * 获取配送员信息
     * @param courierId 配送员id
     * @return Courier
     * @author Max
     * @since 2017/9/18
     */
    public Courier getCourierInfoById(Integer courierId){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "select * from cola_courier where courier_id = ? and status=1";
            return getQueryRunner().query(conn,sql,new BeanHandler<Courier>(Courier.class),courierId);
        }catch (Exception e){
            logger.error("获取配送员信息异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }

    /**
     * 检查配送员手机号是否存在
     * @param mobile 手机号
     * @return true 可以添加；false不可添加
     * @author Max
     * @since 2017/9/19
     */
    public boolean checkCourierMobile(String mobile){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Integer a = 1;
        Integer b = 1;
        try{
            conn = DBControl.getConnection();
            String sql = "select count(mep_id) from cola_mep where mep_mobile =?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,mobile);
            rs = ps.executeQuery();
            if(rs.next()){
                a = rs.getInt(1);
            }
            DbUtils.closeQuietly(null,ps,rs);
            String sql2 = "select count(courier_id) from cola_courier where courier_mobile =? and status >-2";
            ps = conn.prepareStatement(sql2);
            ps.setString(1,mobile);
            rs = ps.executeQuery();
            if(rs.next()){
                b = rs.getInt(1);
            }
            return a<1 && b<1;
        }catch (Exception e){
            logger.error("检查配送员手机号是否存在异常！",e);
        }finally {
            DbUtils.closeQuietly(conn,ps,rs);
        }
        return false;
    }


    /**
     * 获取订单列表
     * @param courierId 配送员id
     * @author Max
     * @since 2017/9/17
     */
    public List<Order> getCourierOrderList(Integer courierId){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "select a.*,b.courier_name,b.courier_mobile,c.customer_name  from cola_order a left join cola_courier b on a.courier_id = b.courier_id " +
                    " left join cola_customer c on a.customer_id = c.customer_id " +
                    " where a.courier_id =? and a.order_status<>'WAITING_FOR_RECEIVING_ORDER' order by a.create_time desc";
            return getQueryRunner().query(conn,sql,new BeanListHandler<Order>(Order.class),courierId);
        }catch (Exception e){
            logger.error("获取全部配送员订单列表异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }

    /**
     * 获取订单详情列表
     * @param courierId 配送员Id
     * @return List<OrderDetail>
     * @author Max
     * @since 2017/9/17
     */
    public List<OrderDetail> getCourierOrderDetailList(Integer courierId){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "select b.* from cola_order a INNER JOIN cola_order_detail b on a.order_no = b.order_no where a.courier_id =?";
            return getQueryRunner().query(conn,sql,new BeanListHandler<OrderDetail>(OrderDetail.class),courierId);
        }catch (Exception e){
            logger.error("获取全部配送员订单列表异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }


    /**
     * 根据订单编号获取促销信息
     * @param orderNo 订单编号
     * @return JoinSaleActivity
     * @author Max
     * @since 2017/9/20
     */
    public JoinSaleActivity getJoinSaleActivityByOrderNo(String orderNo){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();

            String sql = "select * from cola_sale_join where order_no = ?";

            return getQueryRunner().query(conn,sql,new BeanHandler<JoinSaleActivity>(JoinSaleActivity.class),orderNo);

        }catch (Exception e){
            logger.error("根据订单编号获取促销信息异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }

    /**
     * 更新签收订单
     * @param order
     * @return true;false
     * @author Max
     * @since 2017/9/20
     */
    public boolean updateFinalOrder(Order order){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            //更新主订单
            boolean a = updateMainOrder(conn,order);
            //更新订单详情
            boolean b = updateOrderDetail(conn,order);

            return a && b;
        }catch (Exception e){
            logger.error("更新签收订单异常！",e);
            if(conn!=null){
                try{
                    conn.rollback();
                }catch (Exception w){
                    logger.error("更新签收订单异常回滚异常！",e);
                }
            }
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }


    /**
     * 更新主订单
     * @param conn 链接
     * @param order 订单
     * @return true;false
     * @author Max
     * @since 2017/9/20
     */
    public boolean updateMainOrder(Connection conn,Order order) throws Exception {
        try{
            String sql = "update cola_order set is_price_off =?, off_price_final=? ,order_price_final =?, goods_num_final =?,order_status =? " +
                    " where order_no = ?";
            return getQueryRunner().update(conn,sql,order.getIs_price_off(),order.getOff_price(),order.getOrder_price_final()
                    ,order.getGoods_num_final(), OrderType.FINISHED.getValue(),order.getOrder_no())>0;
        }catch (Exception e){
            logger.error("更新主订单异常！",e);
            throw e;
        }
    }


    /**
     * 更新订单详情
     * @param conn 链接
     * @param order 订单
     * @return true;false
     * @author Max
     * @since 2017/9/20
     */
    public boolean updateOrderDetail(Connection conn,Order order) throws Exception {
        try{
            String sql = "update cola_order_detail set goods_num_final =?, goods_price_final=? " +
                    " where order_no = ? and goods_no =?";
            List<OrderDetail> list = order.getOrder_details();
            Object[][] params= new Object[list.size()][4];

            for(int i=0; i<list.size(); i++) {
                params[i][0] = list.get(i).getGoods_num_final();
                params[i][1] = list.get(i).getGoods_price_final();
                params[i][2] = list.get(i).getOrder_no();
                params[i][3] = list.get(i).getGoods_no();
            }
            return getQueryRunner().batch(conn,sql,params).length>0;
        }catch (Exception e){
            logger.error("更新主订单异常！",e);
            throw e;
        }
    }
}
